package com.supermap.wzhy.module.mlk.service;

import com.supermap.wzhy.data.OpStatus;
import com.supermap.wzhy.module.fr.dao.FrDao;
import com.supermap.wzhy.module.fr.dao.FrPushDataDao;
import com.supermap.wzhy.module.fr.service.FrPushDataToMlkService;
import com.supermap.wzhy.module.mlk.dao.MlDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * Created by Sun'fei on 17-2-17.
 */
@Service
public class MlkPushDataToFrService {

    @Autowired
    FrPushDataDao dao;

    @Autowired
    MlDao mlDao;

    @Autowired
    FrDao frDao;

    @Autowired
    FrPushDataToMlkService frService;

    @Autowired
    MlkFillAndImportExcelService fillService;


    final String BASEINFO = "YZYM_FB_TJ_BASEINFO";


    public OpStatus delMlkData(String date){
        OpStatus op = new OpStatus();
        String sql = "";
        if(date != ""){
            sql = "delete from "+BASEINFO+" where S_EXT_TIMESTAMP = to_date('"+date+"','%Y-%m-%d')";
        }else {
            sql = "delete from "+BASEINFO;
        }
        try {
            System.out.println(sql);
            frDao.execute(sql);
            op.setStatus(true);
            op.setMsg("删除 "+date+" 企业信息成功!");
        }catch (Exception e){
            op.setMsg("删除 "+date+" 企业信息失败!");
            System.out.println("删除失败");
            e.printStackTrace();
        }
        return op;
    }


    /**
     * 推送名录库数据
     * @param tableCode 推送表代码
     * @param tableName 推送表中文名称
     * @param startTime 开始时间
     * @param endTime   结束时间
     * @return 推送结果
     */
    public boolean pushDataToFr(String username,String tableCode,String tableName,String startTime,String endTime){
        System.out.println("导入数据到法人库！");
        boolean boo = false;
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        String [] names = tableName.split(",");
        String [] strings = tableCode.split(",");

        startTime = startTime==""?"2000-01-01":startTime;
        endTime = endTime==""?frService.getCurrentDate():endTime;

        for(int i = 0;i<strings.length;i++){
          /*  String [] idenCode = fillService.getIdenCode(strings[i]);
            String iden_code = "";
            //得到字段集
            for(String str:idenCode){
                iden_code += str+",";
            }
            //字段
            iden_code = iden_code.substring(0,iden_code.length()-1);*/

            String iden_code = getIdenCode(names[i]);

            //数据
            List<Object []> list = getData(names[i],iden_code,startTime,endTime);
            //推送
            int count = push(list,strings[i],iden_code);
            //添加日志
            try{
                if(count > 0){
                    frService.addLog(username,strings[i].toString(),names[i].toString(),
                            count,"2",sdf.parse(startTime),sdf.parse(endTime));
                }
            }catch (ParseException e){
                System.out.println("日期转换异常");
            }
        }
        return boo;
    }


    /**
     * 推送数据
     * @param lists 数据集
     * @param table 表名
     * @param iden_code 字段集
     */
    private int push(List<Object []> lists,String table,String iden_code){
        int count = 0;
        //遍历数据结果集
        if(lists.size()>0){
            for(int k = 0;k<lists.size();k++){
                Object [] obj = lists.get(k);
                String val = "";
                for(Object s:obj){
                    if(s != null){
                        val += "'"+s.toString()+"',";
                    }else{
                        val += "'',";
                    }
                }
                val = val.substring(0,val.length()-1);

                String [] iden = iden_code.split(",");
                String [] value = val.split(",");
                String val2 = "";
                if(iden.length == value.length){
                    //格式化sql语句的时间
                    for(int i = 0;i<iden.length;i++){
                        //判断字段是否是date类型
                        if(getDateType(table, iden[i])){
                            //格式化字符串 防止出现datetime类型 1900-01-01 00:00:00.0的情况
                            if(value[i].contains("00.0")){
                                val2 += "to_date("+value[i].substring(0,value[i].length()-3)+"','%Y-%m-%d'),";
                            }else{
                                val2 += "to_date("+value[i]+",'%Y-%m-%d'),";
                            }
                        }else{
                            val2 += value[i]+",";
                        }
                    }
                    val2 = val2.substring(0,val2.length()-1);
                }

                SimpleDateFormat sdf = new SimpleDateFormat("YYYY-MM-dd");
                String today = sdf.format(new Date());
                today = "to_date('"+today+"','%Y-%m-%d')";

                //插入数据sql
                String sql = "insert into "+table+"("+iden_code+",S_EXT_TIMESTAMP) values ("+val2+","+today+")";
                System.out.println(sql);
                try{
                    //循环执行sql
                    frDao.execute(sql);
                    count++;
                }catch (Exception e){
                    System.out.println("sql执行异常！");
                }
            }
            System.out.println(table+" 表共插入 "+count+" 条数据!");
        }else{
            System.out.println(table+" 表共插入 "+count+" 条数据!");
            System.out.println("导入数据为空！");
        }

        return count;
    }


    /**
     * 判断字段是否是日期类型
     * @param table
     * @param iden_code
     * @return
     */
    private boolean getDateType(String table,String iden_code){
        boolean boo = false;
        String sl = "select mitmid from t_micro_tablemeta where table_name = ?";
        int mitmid = Integer.valueOf(dao.query(sl, table).get(0).toString());
        String sql = "select iden_type from t_micro_idenmeta where mitmid = ? and iden_code = ?";
        int type = Integer.valueOf(dao.query(sql, mitmid, iden_code).get(0).toString());
        // 3是date类型
        if(type == 3){
            boo = true;
        }
        return boo;
    }


    /**
     * 得到推送数据集（oracle）
     * @param tableName
     * @return
     */
    private List<Object[]> getData(String tableName,String iden_code,String startTime,String endTime){
        startTime = "to_date('"+startTime+"','YYYY-MM-DD HH24:MI:SS')";
        endTime = "to_date('"+endTime+"','YYYY-MM-DD HH24:MI:SS')";

        //String iden_code = getIdenCode(tableName);

        //String table = getMlkTable(tableName);
        String table_code = getMlkCode(iden_code,tableName);

        String sql ="select "+table_code+" from "+tableName+" where 状态 != '剔除'" +
                " and length(nvl(企业唯一标识,' '))>18 and 可提供给工商='1'";
                //"where s_ext_timestamp >= "+startTime+" and s_ext_timestamp <= "+endTime;
        System.out.println(sql);
        List<Object[]> list = mlDao.query(sql);
        System.out.println(tableName+" 表符合条件的数据共有 "+list.size()+" 行！");
        return list;
    }

    /**
     * 获得名录库推送字段
     * @param iden_code
     * @param table
     * @return
     */
    private String getMlkCode(String iden_code,String table){
        String iden_name = "";
        String sql = "select mitmid from t_micro_tablemeta where name = ?";
        List table_list = dao.query(sql,table);
        String mitmid = table_list.get(0).toString();
        String sql_mlk = "select iden_name from t_micro_idenmeta where iden_code = ? and mitmid = ?";
        String str [] = iden_code.split(",");
        for(int i = 0;i < str.length;i++){
            List li = dao.query(sql_mlk,str[i],mitmid);
            iden_name += li.get(0).toString()+",";
        }
        //System.out.println(iden_code +" > "+iden_name);
        iden_name = iden_name.substring(0,iden_name.length()-1);
        return iden_name;
    }


    /**
     * 根据表名获取需要的指标代码
     * @param tableName 表名
     * @return
     */
    public String getIdenCode(String tableName){
        String sql = "select mitmid from t_micro_tablemeta where name = ?";
        List li = dao.query(sql,tableName);
        String str = "";
        if(li.size() > 0){
            int mitmid = Integer.valueOf(li.get(0).toString());
            String sql2 = "select iden_code from t_micro_idenmeta where mitmid = ?";
            List list = dao.query(sql2,mitmid);
            Object [] obj = list.toArray();

            //Object[]转换成String[]

            for(int i = 0;i<obj.length;i++){
                str += obj[i].toString()+",";
            }
            str = str.substring(0,str.length()-1);
        }
        return  str;
    }


    /**
     * 获得名录库表名
     * @param table
     * @return
     */
    private String getMlkTable(String table){
        String sql = "select name from t_micro_tablemeta where table_name = ?";
        List li = dao.query(sql,table);
        System.out.println(li.get(0).toString());
        return li.get(0).toString();
    }


}
